<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<link rel="STYLESHEET" href="lib.css" type='text/css' />
<link rel="SHORTCUT ICON" href="../icons/pyfav.png" type="image/png" />
<link rel='start' href='../index.html' title='Python documentation Index' />
<link rel="first" href="lib.html" title='Python library Reference' />
<link rel='contents' href='contents.html' title="Contents" />
<link rel='index' href='genindex.html' title='Index' />
<link rel='last' href='about.html' title='About this document...' />
<link rel='help' href='about.html' title='About this document...' />
<link rel="next" href="sqlite3-Cursor-Objects.html" />
<link rel="prev" href="sqlite3-Module-Contents.html" />
<link rel="parent" href="module-sqlite3.html" />
<link rel="next" href="sqlite3-Cursor-Objects.html" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name='aesop' content='information' />
<title>13.13.2 Connection Objects </title>
</head>
<body>
<div class="navigation">
<div id='top-navigation-panel' xml:id='top-navigation-panel'>
<table align="center" width="100%" cellpadding="0" cellspacing="2">
<tr>
<td class='online-navigation'><a rel="prev" title="13.13.1 module functions and"
  href="sqlite3-Module-Contents.html"><img src='../icons/previous.png'
  border='0' height='32'  alt='Previous Page' width='32' /></a></td>
<td class='online-navigation'><a rel="parent" title="13.13 sqlite3  "
  href="module-sqlite3.html"><img src='../icons/up.png'
  border='0' height='32'  alt='Up one Level' width='32' /></a></td>
<td class='online-navigation'><a rel="next" title="13.13.3 cursor Objects"
  href="sqlite3-Cursor-Objects.html"><img src='../icons/next.png'
  border='0' height='32'  alt='Next Page' width='32' /></a></td>
<td align="center" width="100%">Python Library Reference</td>
<td class='online-navigation'><a rel="contents" title="Table of Contents"
  href="contents.html"><img src='../icons/contents.png'
  border='0' height='32'  alt='Contents' width='32' /></a></td>
<td class='online-navigation'><a href="modindex.html" title="Module Index"><img src='../icons/modules.png'
  border='0' height='32'  alt='Module Index' width='32' /></a></td>
<td class='online-navigation'><a rel="index" title="Index"
  href="genindex.html"><img src='../icons/index.png'
  border='0' height='32'  alt='Index' width='32' /></a></td>
</tr></table>
<div class='online-navigation'>
<b class="navlabel">Previous:</b>
<a class="sectref" rel="prev" href="sqlite3-Module-Contents.html">13.13.1 Module functions and</a>
<b class="navlabel">Up:</b>
<a class="sectref" rel="parent" href="module-sqlite3.html">13.13 sqlite3  </a>
<b class="navlabel">Next:</b>
<a class="sectref" rel="next" href="sqlite3-Cursor-Objects.html">13.13.3 Cursor Objects</a>
</div>
<hr /></div>
</div>
<!--End of Navigation Panel-->

<h2><a name="SECTION00151320000000000000000"></a><a name="sqlite3-Connection-Objects"></a>
<br>
13.13.2 Connection Objects 
</h2>

<p>
A <tt class="class">Connection</tt> instance has the following attributes and methods:

<p>
<a name="sqlite3-Connection-IsolationLevel"></a><dl><dt><b><tt id='l2h-2559' xml:id='l2h-2559' class="member">isolation_level</tt></b></dt>
<dd>
  Get or set the current isolation level. None for autocommit mode or one of
  "DEFERRED", "IMMEDIATE" or "EXLUSIVE". See ``Controlling Transactions'', 
  section&nbsp;<a href="sqlite3-Controlling-Transactions.html#sqlite3-Controlling-Transactions">13.13.5</a>, for a more detailed explanation.
</dl>

<p>
<dl><dt><table cellpadding="0" cellspacing="0"><tr valign="baseline">
  <td><nobr><b><tt id='l2h-2560' xml:id='l2h-2560' class="method">cursor</tt></b>(</nobr></td>
  <td><var></var><big>[</big><var>cursorClass</var><big>]</big><var></var>)</td></tr></table></dt>
<dd>
  The cursor method accepts a single optional parameter <var>cursorClass</var>.
  If supplied, this must be a custom cursor class that extends 
  <tt class="class">sqlite3.Cursor</tt>.
</dl>

<p>
<dl><dt><table cellpadding="0" cellspacing="0"><tr valign="baseline">
  <td><nobr><b><tt id='l2h-2561' xml:id='l2h-2561' class="method">execute</tt></b>(</nobr></td>
  <td><var>sql, </var><big>[</big><var>parameters</var><big>]</big><var></var>)</td></tr></table></dt>
<dd>
This is a nonstandard shortcut that creates an intermediate cursor object by
calling the cursor method, then calls the cursor's <tt class="method">execute</tt> method with the
parameters given.
</dl>

<p>
<dl><dt><table cellpadding="0" cellspacing="0"><tr valign="baseline">
  <td><nobr><b><tt id='l2h-2562' xml:id='l2h-2562' class="method">executemany</tt></b>(</nobr></td>
  <td><var>sql, </var><big>[</big><var>parameters</var><big>]</big><var></var>)</td></tr></table></dt>
<dd>
This is a nonstandard shortcut that creates an intermediate cursor object by
calling the cursor method, then calls the cursor's <tt class="method">executemany</tt> method with the
parameters given.
</dl>

<p>
<dl><dt><table cellpadding="0" cellspacing="0"><tr valign="baseline">
  <td><nobr><b><tt id='l2h-2563' xml:id='l2h-2563' class="method">executescript</tt></b>(</nobr></td>
  <td><var>sql_script</var>)</td></tr></table></dt>
<dd>
This is a nonstandard shortcut that creates an intermediate cursor object by
calling the cursor method, then calls the cursor's <tt class="method">executescript</tt> method with the
parameters given.
</dl>

<p>
<dl><dt><table cellpadding="0" cellspacing="0"><tr valign="baseline">
  <td><nobr><b><tt id='l2h-2564' xml:id='l2h-2564' class="method">create_function</tt></b>(</nobr></td>
  <td><var>name, num_params, func</var>)</td></tr></table></dt>
<dd>

<p>
Creates a user-defined function that you can later use from within SQL
statements under the function name <var>name</var>. <var>num_params</var> is the number
of parameters the function accepts, and <var>func</var> is a Python callable that is
called as the SQL function.

<p>
The function can return any of the types supported by SQLite: unicode, str,
int, long, float, buffer and None.

<p>
Example:

<p>
<div class="verbatim">
<pre>import sqlite3
import md5

def md5sum(t):
    return md5.md5(t).hexdigest()

con = sqlite3.connect(":memory:")
con.create_function("md5", 1, md5sum)
cur = con.cursor()
cur.execute("select md5(?)", ("foo",))
print cur.fetchone()[0]
</pre>
<div class="footer">
<a href="md5func.txt" type="text/plain">Download as text (original file name: <span class="file">sqlite3/md5func.py</span>).</a>
</div></div>
</dl>

<p>
<dl><dt><table cellpadding="0" cellspacing="0"><tr valign="baseline">
  <td><nobr><b><tt id='l2h-2565' xml:id='l2h-2565' class="method">create_aggregate</tt></b>(</nobr></td>
  <td><var>name, num_params, aggregate_class</var>)</td></tr></table></dt>
<dd>

<p>
Creates a user-defined aggregate function.

<p>
The aggregate class must implement a <code>step</code> method, which accepts the
number of parameters <var>num_params</var>, and a <code>finalize</code> method which
will return the final result of the aggregate.

<p>
The <code>finalize</code> method can return any of the types supported by SQLite:
unicode, str, int, long, float, buffer and None.

<p>
Example:

<p>
<div class="verbatim">
<pre>import sqlite3

class MySum:
    def __init__(self):
        self.count = 0

    def step(self, value):
        self.count += value

    def finalize(self):
        return self.count

con = sqlite3.connect(":memory:")
con.create_aggregate("mysum", 1, MySum)
cur = con.cursor()
cur.execute("create table test(i)")
cur.execute("insert into test(i) values (1)")
cur.execute("insert into test(i) values (2)")
cur.execute("select mysum(i) from test")
print cur.fetchone()[0]
</pre>
<div class="footer">
<a href="mysumaggr.txt" type="text/plain">Download as text (original file name: <span class="file">sqlite3/mysumaggr.py</span>).</a>
</div></div>
</dl>

<p>
<dl><dt><table cellpadding="0" cellspacing="0"><tr valign="baseline">
  <td><nobr><b><tt id='l2h-2566' xml:id='l2h-2566' class="method">create_collation</tt></b>(</nobr></td>
  <td><var>name, callable</var>)</td></tr></table></dt>
<dd>

<p>
Creates a collation with the specified <var>name</var> and <var>callable</var>. The
callable will be passed two string arguments. It should return -1 if the first
is ordered lower than the second, 0 if they are ordered equal and 1 if the
first is ordered higher than the second.  Note that this controls sorting
(ORDER BY in SQL) so your comparisons don't affect other SQL operations.

<p>
Note that the callable will get its parameters as Python bytestrings, which
will normally be encoded in UTF-8.

<p>
The following example shows a custom collation that sorts "the wrong way":

<p>
<div class="verbatim">
<pre>import sqlite3

def collate_reverse(string1, string2):
    return -cmp(string1, string2)

con = sqlite3.connect(":memory:")
con.create_collation("reverse", collate_reverse)

cur = con.cursor()
cur.execute("create table test(x)")
cur.executemany("insert into test(x) values (?)", [("a",), ("b",)])
cur.execute("select x from test order by x collate reverse")
for row in cur:
    print row
con.close()
</pre>
<div class="footer">
<a href="collation_reverse.txt" type="text/plain">Download as text (original file name: <span class="file">sqlite3/collation_reverse.py</span>).</a>
</div></div>

<p>
To remove a collation, call <code>create_collation</code> with None as callable:

<p>
<div class="verbatim"><pre>
    con.create_collation("reverse", None)
</pre></div>
</dl>

<p>
<dl><dt><table cellpadding="0" cellspacing="0"><tr valign="baseline">
  <td><nobr><b><tt id='l2h-2567' xml:id='l2h-2567' class="method">interrupt</tt></b>(</nobr></td>
  <td><var></var>)</td></tr></table></dt>
<dd>

<p>
You can call this method from a different thread to abort any queries that
might be executing on the connection. The query will then abort and the caller
will get an exception.
</dl>

<p>
<dl><dt><table cellpadding="0" cellspacing="0"><tr valign="baseline">
  <td><nobr><b><tt id='l2h-2568' xml:id='l2h-2568' class="method">set_authorizer</tt></b>(</nobr></td>
  <td><var>authorizer_callback</var>)</td></tr></table></dt>
<dd>

<p>
This routine registers a callback. The callback is invoked for each attempt to
access a column of a table in the database. The callback should return
<tt class="constant">SQLITE_OK</tt> if access is allowed, <tt class="constant">SQLITE_DENY</tt> if the entire
SQL statement should be aborted with an error and <tt class="constant">SQLITE_IGNORE</tt> if
the column should be treated as a NULL value. These constants are available in
the <tt class="module">sqlite3</tt> module.

<p>
The first argument to the callback signifies what kind of operation is to be
authorized. The second and third argument will be arguments or <tt class="constant">None</tt>
depending on the first argument. The 4th argument is the name of the database
("main", "temp", etc.) if applicable. The 5th argument is the name of the
inner-most trigger or view that is responsible for the access attempt or
<tt class="constant">None</tt> if this access attempt is directly from input SQL code.

<p>
Please consult the SQLite documentation about the possible values for the first
argument and the meaning of the second and third argument depending on the
first one. All necessary constants are available in the <tt class="module">sqlite3</tt>
module.
</dl>

<p>
<dl><dt><b><tt id='l2h-2569' xml:id='l2h-2569' class="member">row_factory</tt></b></dt>
<dd>
  You can change this attribute to a callable that accepts the cursor and
  the original row as a tuple and will return the real result row.  This
  way, you can implement more advanced ways of returning results, such 
  as returning an object that can also access columns by name.

<p>
Example:

<p>
<div class="verbatim">
<pre>import sqlite3

def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

con = sqlite3.connect(":memory:")
con.row_factory = dict_factory
cur = con.cursor()
cur.execute("select 1 as a")
print cur.fetchone()["a"]
</pre>
<div class="footer">
<a href="row_factory.txt" type="text/plain">Download as text (original file name: <span class="file">sqlite3/row_factory.py</span>).</a>
</div></div>

<p>
If returning a tuple doesn't suffice and you want name-based
  access to columns, you should consider setting <tt class="member">row_factory</tt> to the
  highly-optimized <tt class="class">sqlite3.Row</tt> type. <tt class="class">Row</tt> provides both
  index-based and case-insensitive name-based access to columns with almost
  no memory overhead. It will probably be better than your own custom 
  dictionary-based approach or even a db_row based solution.
  </dl>

<p>
<dl><dt><b><tt id='l2h-2570' xml:id='l2h-2570' class="member">text_factory</tt></b></dt>
<dd>
  Using this attribute you can control what objects are returned for the
  TEXT data type. By default, this attribute is set to <tt class="class">unicode</tt> and
  the <tt class="module">sqlite3</tt> module will return Unicode objects for TEXT. If you want to return
  bytestrings instead, you can set it to <tt class="class">str</tt>.

<p>
For efficiency reasons, there's also a way to return Unicode objects only
  for non-ASCII data, and bytestrings otherwise. To activate it, set this
  attribute to <tt class="constant">sqlite3.OptimizedUnicode</tt>.

<p>
You can also set it to any other callable that accepts a single bytestring
  parameter and returns the resulting object.

<p>
See the following example code for illustration:

<p>
<div class="verbatim">
<pre>import sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()

# Create the table
con.execute("create table person(lastname, firstname)")

AUSTRIA = u"\xd6sterreich"

# by default, rows are returned as Unicode
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert row[0] == AUSTRIA

# but we can make pysqlite always return bytestrings ...
con.text_factory = str
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert type(row[0]) == str
# the bytestrings will be encoded in UTF-8, unless you stored garbage in the
# database ...
assert row[0] == AUSTRIA.encode("utf-8")

# we can also implement a custom text_factory ...
# here we implement one that will ignore Unicode characters that cannot be
# decoded from UTF-8
con.text_factory = lambda x: unicode(x, "utf-8", "ignore")
cur.execute("select ?", ("this is latin1 and would normally create errors" + u"\xe4\xf6\xfc".encode("latin1"),))
row = cur.fetchone()
assert type(row[0]) == unicode

# pysqlite offers a builtin optimized text_factory that will return bytestring
# objects, if the data is in ASCII only, and otherwise return unicode objects
con.text_factory = sqlite3.OptimizedUnicode
cur.execute("select ?", (AUSTRIA,))
row = cur.fetchone()
assert type(row[0]) == unicode

cur.execute("select ?", ("Germany",))
row = cur.fetchone()
assert type(row[0]) == str
</pre>
<div class="footer">
<a href="text_factory.txt" type="text/plain">Download as text (original file name: <span class="file">sqlite3/text_factory.py</span>).</a>
</div></div>
</dl>

<p>
<dl><dt><b><tt id='l2h-2571' xml:id='l2h-2571' class="member">total_changes</tt></b></dt>
<dd>
  Returns the total number of database rows that have been modified, inserted,
  or deleted since the database connection was opened.
</dl>

<p>

<div class="navigation">
<div class='online-navigation'>
<p></p><hr />
<table align="center" width="100%" cellpadding="0" cellspacing="2">
<tr>
<td class='online-navigation'><a rel="prev" title="13.13.1 module functions and"
  href="sqlite3-Module-Contents.html"><img src='../icons/previous.png'
  border='0' height='32'  alt='Previous Page' width='32' /></a></td>
<td class='online-navigation'><a rel="parent" title="13.13 sqlite3  "
  href="module-sqlite3.html"><img src='../icons/up.png'
  border='0' height='32'  alt='Up one Level' width='32' /></a></td>
<td class='online-navigation'><a rel="next" title="13.13.3 cursor Objects"
  href="sqlite3-Cursor-Objects.html"><img src='../icons/next.png'
  border='0' height='32'  alt='Next Page' width='32' /></a></td>
<td align="center" width="100%">Python Library Reference</td>
<td class='online-navigation'><a rel="contents" title="Table of Contents"
  href="contents.html"><img src='../icons/contents.png'
  border='0' height='32'  alt='Contents' width='32' /></a></td>
<td class='online-navigation'><a href="modindex.html" title="Module Index"><img src='../icons/modules.png'
  border='0' height='32'  alt='Module Index' width='32' /></a></td>
<td class='online-navigation'><a rel="index" title="Index"
  href="genindex.html"><img src='../icons/index.png'
  border='0' height='32'  alt='Index' width='32' /></a></td>
</tr></table>
<div class='online-navigation'>
<b class="navlabel">Previous:</b>
<a class="sectref" rel="prev" href="sqlite3-Module-Contents.html">13.13.1 Module functions and</a>
<b class="navlabel">Up:</b>
<a class="sectref" rel="parent" href="module-sqlite3.html">13.13 sqlite3  </a>
<b class="navlabel">Next:</b>
<a class="sectref" rel="next" href="sqlite3-Cursor-Objects.html">13.13.3 Cursor Objects</a>
</div>
</div>
<hr />
<span class="release-info">Release 2.5.1, documentation updated on 18th April, 2007.</span>
</div>
<!--End of Navigation Panel-->
<address>
See <i><a href="about.html">About this document...</a></i> for information on suggesting changes.
</address>
</body>
</html>
